package org.abacus.platform.database.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.abacus.platform.data.ConnectionProvider;
import org.abacus.platform.database.Column;
import org.abacus.platform.database.DatabaseException;
import org.abacus.platform.database.DatabaseManager;
import org.abacus.platform.database.Datasource;
import org.abacus.platform.database.Table;
import org.abacus.platform.database.TableGroup;

public class DatabaseManagerImpl implements DatabaseManager {
	static String TABLE_PREFIX = "T_";

	static String COLUMN_PREFIX = "C_";

	private static DatabaseManager databaseManager;

	private static ConnectionProvider connectionProvider;

	private DatabaseManagerImpl(ConnectionProvider connectionProvider) {
		super();
		DatabaseManagerImpl.connectionProvider = connectionProvider;
	}

	public synchronized final static DatabaseManager getInstance(ConnectionProvider connectionProvider) {
		if (null == databaseManager) {
			databaseManager = new DatabaseManagerImpl(connectionProvider);
		}

		return databaseManager;
	}

	public boolean create(Table arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.create(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(Table[] args) throws DatabaseException {
		for (Table arg0 : args) {
			create(arg0);
		}
		return true;
	}

	public boolean drop(Table arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.drop(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean alter(Table arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.alter(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean rename(Table arg0, String nowname) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.rename(arg0, nowname);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(Column arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.create(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(Column[] args) throws DatabaseException {
		for (Column arg0 : args) {
			create(arg0);
		}
		return true;
	}

	public boolean drop(Column arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.drop(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean alter(Column arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.alter(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean change(Column arg0, String nowname) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.change(arg0, nowname);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(Datasource arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.create(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(Datasource[] args) throws DatabaseException {
		for (Datasource arg0 : args) {
			create(arg0);
		}
		return true;
	}

	public boolean drop(Datasource arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.drop(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean alter(Datasource arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.alter(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(TableGroup arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.create(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean create(TableGroup[] args) throws DatabaseException {
		for (TableGroup arg0 : args) {
			create(arg0);
		}
		return true;
	}

	public boolean drop(TableGroup arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.drop(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public boolean alter(TableGroup arg0) throws DatabaseException {
		Transaction tx = JdbcTransaction.getInstance();
		Executor exe = null;
		try {
			exe = JdbcExecutor.getInstance();
			tx.begin();
			exe.start();
			String sql = DatabaseHelper.alter(arg0);
			exe.execute(sql);
			exe.close();
			tx.commit();
			return true;
		} catch (SQLException e) {
			try {
				tx.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally {
			if (null != exe) {
				try {
					exe.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public interface Transaction {
		void begin() throws SQLException;

		void commit() throws SQLException;

		void rollback() throws SQLException;
	}

	public static class JdbcTransaction implements Transaction {
		private static ThreadLocal<Transaction> tl = new ThreadLocal<Transaction>();
		private Connection conn;

		private boolean toggleAutoCommit;

		private boolean begun;

		boolean rolledBack;

		boolean committed;

		boolean commitFailed;

		public synchronized final static Transaction getInstance() {
			if (null == tl.get()) {
				tl.set(new JdbcTransaction());
			}
			return tl.get();
		}

		public void begin() throws SQLException {
			if (begun) {
				return;
			}
			try {
				conn = connectionProvider.getConnection();
			} catch (SQLException e) {
				throw e;
			} finally {
				if (null != conn && !conn.isClosed()) {
					toggleAutoCommit = conn.getAutoCommit();
					if (toggleAutoCommit) {
						conn.setAutoCommit(false);
					}
					begun = true;
					committed = false;
					rolledBack = false;
				}
			}
		}

		public void commit() throws SQLException {
			if (!begun) {
				return;
			}
			try {
				if (null != conn && !conn.isClosed()) {
					conn.commit();
				}
			} catch (SQLException e) {
				throw e;
			} finally {
				setAutoCommit();
				begun = false;
				committed = true;
				rolledBack = false;
			}
		}

		public void rollback() throws SQLException {
			if (!begun) {
				return;
			}
			try {
				if (null != conn && !conn.isClosed()) {
					conn.rollback();
				}
			} catch (SQLException e) {
				throw e;
			} finally {
				setAutoCommit();
				begun = false;
				committed = false;
				rolledBack = true;
			}
		}

		private void setAutoCommit() throws SQLException {
			try {
				if (toggleAutoCommit) {
					if (null != conn && !conn.isClosed()) {
						conn.setAutoCommit(true);
					}
				}
			} catch (SQLException e) {
				throw e;
			}
		}
	}

	public interface Executor {
		void start() throws SQLException;

		void close() throws SQLException;

		boolean execute(String sql) throws SQLException;

		boolean[] execute(String[] sqls) throws SQLException;

		int executeUpdate(String sql) throws SQLException;

		int[] executeUpdate(String[] sqls) throws SQLException;
	}

	public static class JdbcExecutor implements Executor {
		private static ThreadLocal<Executor> tl = new ThreadLocal<Executor>();
		private Connection conn;
		private Statement stmt;

		public synchronized final static Executor getInstance() {
			if (null == tl.get()) {
				tl.set(new JdbcExecutor());
			}
			return (Executor) tl.get();
		}

		public void start() throws SQLException {
			conn = connectionProvider.getConnection();
			if (null != conn) {
				stmt = conn.createStatement();
			}
		}

		public void close() throws SQLException {
			if (null != stmt) {
				stmt.close();
				stmt = null;
			}
		}

		public boolean execute(String sql) throws SQLException {
			if (null == sql) {
				return false;
			}
			boolean flag = false;
			if (null != stmt) {
				flag = stmt.execute(sql);
			}
			return flag;
		}

		public boolean[] execute(String[] sqls) throws SQLException {
			if (null == sqls || (null != sqls && sqls.length < 1)) {
				return new boolean[] {};
			}
			boolean flag[] = new boolean[sqls.length];
			if (null != stmt) {
				for (int i = 0; i < sqls.length; i++) {
					flag[i] = stmt.execute(sqls[i]);
				}
			}
			return flag;
		}

		public int executeUpdate(String sql) throws SQLException {
			if (null == sql) {
				return 0;
			}
			int flag = 0;
			if (null != stmt) {
				flag = stmt.executeUpdate(sql);
			}
			return flag;
		}

		public int[] executeUpdate(String[] sqls) throws SQLException {
			if (null == sqls || (null != sqls && sqls.length < 1)) {
				return new int[] {};
			}
			int[] flag = new int[sqls.length];
			if (null != stmt) {
				for (int i = 0; i < sqls.length; i++) {
					flag[i] = stmt.executeUpdate(sqls[i]);
				}
			}
			return flag;
		}
	}

}
